package com.skp.shaphan.actions;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import org.apache.pdfbox.exceptions.COSVisitorException;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.pdfbox.pdmodel.PDPage;
import org.apache.pdfbox.pdmodel.edit.PDPageContentStream;
import org.apache.pdfbox.pdmodel.font.PDFont;
import org.apache.pdfbox.pdmodel.font.PDType1Font;

import com.skp.job.ProjectJobException;
import com.skp.shaphan.report.BaseReport;
import com.skp.shaphan.report.TableCompareReport;
import com.skp.shaphan.savedConnections.SavedConnections;
import com.skp.shaphan.ui.ActionBasedPanel;
import com.skp.shaphan.ui.connectioncontrols.ConnectionOptions;
import com.skp.shaphan.ui.windows.CompareOutputWindow;

public class CompareTablesAction implements Runnable {
	private int leftConnId;
	private int rightConnId;
	private String tables;
	private CompareOutputWindow output;
	private String outputPath;

	@Override
	public void run() {
		// from http://stackoverflow.com/questions/225337/how-do-i-split-a-string-with-any-whitespace-chars-as-delimiters
		String[] tables = this.tables.split("[\\s,]+");
		
		output.setRecordCount(tables.length);
		
		// start the compare output document
		TableCompareReport outDoc;
		try {
			outDoc = new TableCompareReport();
		} catch (IOException e2) {
			e2.printStackTrace();
			return;
		}
		
		
		Connection lConn;
		Connection rConn;
		try {
			lConn = getConnection(leftConnId);
			rConn = getConnection(rightConnId);
		} catch (ProjectJobException e) {
			e.displayException();
			return;
		}
		
		PreparedStatement st;
		ResultSet rs;
		
		// process each table
		try {
			System.out.println("Tables --");
			for(String table : tables) {
				output.nextRecord(table);
				System.out.println("  - " + table);
				outDoc.addTable(table);

				String lRecName = getRecName(lConn, table);
				output.addinfo("Record: " + lRecName);
				ArrayList<String> lKeys = new ArrayList<String>();
				ArrayList<String> lFields = new ArrayList<String>();
				buildFieldLists(lConn, lRecName, lKeys, lFields);
				String keyList = "";
				for(String key : lKeys) {
					if(!keyList.equals("")) {
						keyList += ", ";
					}
					keyList += key;
				}
				output.addinfo("  Keys: " + keyList); 
				outDoc.setKeys(lKeys);
				String rRecName = getRecName(rConn, table);
				output.addinfo("Record: " + rRecName);
				
				int lCount = 0;
				int rCount = 0;
				try {
					try {
						st = lConn.prepareStatement("select count(*) from " + table);
						rs = st.executeQuery();
						if(rs.next()) {
							lCount = rs.getInt(1);
						}
						rs.close();
						st.close();
					} catch(SQLException e1) {
						output.addinfo("Error accessing left side: " + e1.getLocalizedMessage());
						throw e1;
					}

					try {
						st = rConn.prepareStatement("select count(*) from " + table);
						rs = st.executeQuery();
						if(rs.next()) {
							rCount = rs.getInt(1);
						}
						rs.close();
						st.close();
					} catch(SQLException e1) {
						output.addinfo("Error accessing right side: " + e1.getLocalizedMessage());
						throw e1;
					}
					
					String sql = generateSelect(table, lKeys, lFields);
					System.out.println(sql);
					st = lConn.prepareStatement(sql);
					ResultSet lData = st.executeQuery();
					st = rConn.prepareStatement(sql);
					ResultSet rData = st.executeQuery();
					
					boolean lMore = lData.next();
					boolean rMore = rData.next();
					while(lMore) {
						int compare = compareKeys(lKeys, lData, rData);
						if(lMore) {
							printKeys(lKeys, lData);
						}
						if(rMore) {
							printKeys(lKeys, rData);
						}
						
						boolean lIncrement = false;
						boolean rIncrement = false;
						if(compare == 0) {
							lIncrement = true;
							rIncrement = true;
						} else if(compare > 0) {
							rIncrement = true;
						} else {
							lIncrement = true;
						}
						System.out.println("  ->" + compare + " lIncrement = " + lIncrement + "  rIncrement = " + rIncrement);
						if(lMore && lIncrement) {
							lMore = lData.next();
						}
						if(rMore && rIncrement) {
							rMore = rData.next();
						}
					}
					
					// close all
					lData.close();
					lData.getStatement().close();
					rData.close();
					rData.getStatement().close();
				} catch (SQLException e3) {
					output.addinfo("Compare failed for table: " + table + " -- " + e3.getLocalizedMessage());
					e3.printStackTrace();
				}
				
				output.addinfo("Left Count: " + lCount + "     Right Count: " + rCount);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			outDoc.save(outputPath);
			outDoc.close();
		} catch (COSVisitorException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		System.out.println("<<<<<<<<<<< Finished >>>>>>>>>>>");
	}
	
	private void printKeys(ArrayList<String> keys, ResultSet data) throws SQLException {
		StringBuilder out = new StringBuilder();
		
		for(String key : keys) {
			Object o = data.getObject(key);
			out.append("|");
			out.append(o.toString());
			out.append("(");
			out.append(o.getClass().getName());
			out.append(")");
		}
		
		System.out.println(out.toString());
	}
	
	private String getRecName(Connection conn, String tableName) throws SQLException {
		boolean found = false;
		
		String sql = "SELECT COUNT(*) FROM PSRECDEFN WHERE RECNAME = ?";
		PreparedStatement st = conn.prepareStatement(sql);
		st.setString(1, tableName);
		ResultSet rs = st.executeQuery();
		if(rs.next()) {
			if(rs.getInt(1) >= 1) {
				found = true;
			}
		}
		rs.close();
		st.close();
		
		if(found) {
			return tableName;
		}
		
		String recName = "";
		
		sql = "SELECT RECNAME FROM PSRECDEFN WHERE SQLTABLENAME = ?";
		st = conn.prepareStatement(sql);
		st.setString(1, tableName);
		rs = st.executeQuery();
		if(rs.next()) {
			found = true;
			recName = rs.getString(1);
		}
		rs.close();
		st.close();
		
		if(found) {
			return recName;
		}
		
		recName = tableName.toUpperCase();
		if(recName.startsWith("PS_")) {
			recName = recName.substring(3);
		}
		
		return recName;
	}
	
	private void buildFieldLists(Connection conn, String recName, ArrayList<String> keys, ArrayList<String> fields) throws SQLException {
		String sql = "SELECT * FROM PSKEYDEFN WHERE RECNAME = ? AND INDEXID = '_'";
		PreparedStatement st = conn.prepareStatement(sql);
		st.setString(1, recName);
		ResultSet rs = st.executeQuery();
		while(rs.next()) {
			keys.add(rs.getString("FIELDNAME"));
		}
		rs.close();
		st.close();
		
		sql = "SELECT * FROM PSRECFIELDALL WHERE RECNAME = ?";
		st = conn.prepareStatement(sql);
		st.setString(1, recName);
		rs = st.executeQuery();
		while(rs.next()) {
			if(!keys.contains(rs.getString("FIELDNAME"))) {
				fields.add(rs.getString("FIELDNAME"));
			}
		}
		rs.close();
		st.close();
	}
	
	private int compareKeys(ArrayList<String> keys, ResultSet lData, ResultSet rData) throws SQLException {
		int compare = 0;
		for(String key : keys) {
			Object l = lData.getObject(key);
			Object r = rData.getObject(key);
			if(l instanceof String) {
				compare = ((String)l).compareTo((String)r);
				if(compare != 0) {
					return compare;
				}
			} else if(l instanceof Timestamp) {
				compare = ((Timestamp)l).compareTo((Timestamp)r);
				if(compare != 0) {
					return compare;
				}
			} else {
				throw new SQLException("Couldn't compare: " + l.getClass().getName());
			}
		}
		
		return compare;
	}
	
	private String generateSelect(String tableName, ArrayList<String> keys, ArrayList<String> fields) {
		StringBuilder sql = new StringBuilder("SELECT ");
		boolean first = true;
		
		for(String f : keys) {
			if(first) {
				first = false;
			} else {
				sql.append(", ");
			}
			sql.append(f);
		}
		for(String f : fields) {
			if(first) {
				first = false;
			} else {
				sql.append(", ");
			}
			sql.append(f);
		}
		
		sql.append(" FROM ");
		sql.append(tableName);

		if(keys.size() > 0) {
			sql.append(" ORDER BY ");
			first = true;
			for(String f : keys) {
				if(first) {
					first = false;
				} else {
					sql.append(", ");
				}
				sql.append(f);
			}
		}
		
		return sql.toString();
	}
	
	private Connection getConnection(int connectionId) throws ProjectJobException {
		SavedConnections sc = SavedConnections.getInstance();
		int index = sc.getIndexForID(connectionId);
		ConnectionOptions options;
		try {
			options = (ConnectionOptions) Class.forName(sc.getConnectionClass(index)).newInstance();
		} catch (InstantiationException e) {
			throw new ProjectJobException("Error connecting: " + e.getLocalizedMessage(), e);
		} catch (IllegalAccessException e) {
			throw new ProjectJobException("Error connecting: " + e.getLocalizedMessage(), e);
		} catch (ClassNotFoundException e) {
			throw new ProjectJobException("Error connecting: " + e.getLocalizedMessage(), e);
		}
		options.setConnectionOptions(sc.getConnectionProperties(index));
		return ((ActionBasedPanel)options).getAction().connect();
	}

	public int getLeftConnId() {
		return leftConnId;
	}

	public void setLeftConnId(int leftConnId) {
		this.leftConnId = leftConnId;
	}

	public int getRightConnId() {
		return rightConnId;
	}

	public void setRightConnId(int rightConnId) {
		this.rightConnId = rightConnId;
	}

	public String getTables() {
		return tables;
	}

	public void setTables(String tables) {
		this.tables = tables;
	}

	public CompareOutputWindow getOutput() {
		return output;
	}

	public void setOutput(CompareOutputWindow output) {
		this.output = output;
	}

	public String getOutputPath() {
		return outputPath;
	}

	public void setOutputPath(String outputPath) {
		this.outputPath = outputPath;
	}

}
